package com.crm.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;

import com.crm.entity.Business;
import com.crm.entity.Clientdet;
import com.crm.entity.Opportunity;
import com.crm.entity.Parts;
import com.crm.entity.QuotParts;
import com.crm.entity.Quotation;
import com.crm.util.ApplicationContext;


public class QuotationDAO {
	static Transaction tx;
	protected final Log logger = LogFactory.getLog(getClass());
	
	public  int insert(Quotation q)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
			tx = session.beginTransaction();
			session.saveOrUpdate(q);
			tx.commit();
			return 0;
		}
		catch (Exception e) {
			if (tx!=null) tx.rollback();
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<Quotation> findAllQuots(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			 List<Quotation> custObjs=new ArrayList<Quotation>();
			 List objs=null;
			Query query=session.createQuery("from Quotation where business_id=:busid");
			query.setInteger("busid", busid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((Quotation)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Quotation> findByQuotName(int busid,String quotNumber) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			List<Quotation> custObjs=new ArrayList<Quotation>();
			List objs=null;
			Query query=session.createQuery("from Quotation where business_id=:busid and quotno like '%"+quotNumber+"%'");
			query.setInteger("busid", busid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((Quotation)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Quotation> findQuotByMonYear(int busid,String quotNumber,String mon,String year) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			List<Quotation> custObjs=new ArrayList<Quotation>();
			List objs=null;
			Query query=session.createQuery("from Quotation where business_id=:busid and quotno like '%"+quotNumber+"%' and qutdate like'%"+year+"-"+mon+"%'");
			query.setInteger("busid", busid);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((Quotation)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Long findCount(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Long objs=null;
			Query query=session.createQuery("select count(distinct q.enqid) from Quotation q where q.business_id=:busid");
			query.setInteger("busid", busid);
			objs=(Long) query.uniqueResult();
			return objs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Quotation findById(int i) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Quotation c=(Quotation)session.load(Quotation.class, i);
			return c;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	public  Quotation getQuotById(int id,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation where id=:id and business_id=:busid";
			Query query=session.createQuery(querystr);
			query.setInteger("id", id);
			query.setInteger("busid", busid);
			Object queryresult=query.uniqueResult();
			Quotation q=(Quotation)queryresult;
			return q;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Quotation getQuotByEnq(int enqid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation where enqid=:id order by id desc limit 1";
			Query query=session.createQuery(querystr);
			query.setInteger("id", enqid);
			query.setMaxResults(1);
			Object queryresult=query.uniqueResult();
			Quotation q=(Quotation)queryresult;
			return q;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  Clientdet getquotdet(String quotstr,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			System.out.println("In getQuotDet");
			String querystr="From Clientdet cli where cli.organisation=(select 0rganisation From Opportunity opp where opp.enqnum=:quot and business_id=:busid) and business_id=:busid";
			Query query=session.createQuery(querystr);
			query.setString("quot", quotstr);
			query.setInteger("busid", busid);
			Object queryresult=query.uniqueResult();
			Clientdet cli=(Clientdet)queryresult;
			return cli;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Object> getParts(String enqid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qstr="From Parts p where p.enqNo=:enqnumb and business_id=:busid";
			Query qr=session.createQuery(qstr);
			qr.setString("enqnumb", enqid);
			qr.setInteger("busid", busid);
			List<Object> qresult=qr.list();
			return qresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  long getqutid(String enqType,int busid)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			SimpleDateFormat frmatt=new SimpleDateFormat("dd-MM");
			Calendar dt=Calendar.getInstance();
			String dm=frmatt.format(dt.getTime());
			
			SimpleDateFormat formatt=new SimpleDateFormat("yyyy");
			Calendar dat=Calendar.getInstance();
			String yy=formatt.format(dat.getTime());
			
			SimpleDateFormat formtt=new SimpleDateFormat("MM-yyyy");
			Calendar dy=Calendar.getInstance();
			String my=formtt.format(dy.getTime());
			
			SimpleDateFormat frmat=new SimpleDateFormat("dd-MM-yyyy");
			Calendar dte=Calendar.getInstance();
			String da=frmat.format(dte.getTime());
			
			String qstr="";
			if(enqType.equals("TE")||enqType.equals("CP")||enqType.equals("PE")||enqType.equals("OPTS")){
				qstr="select COUNT(distinct qut.enqid)+1 From Quotation qut where qut.quotno like '%"+enqType+"%"+my+"%' and qut.businessId=:busid";	
				
			}else if(enqType.equals("BQ")){
				logger.info("hello we are in first if"+enqType);
				if(dm.equals("01-04")){
					qstr="select COUNT(distinct qut.enqid)+1 From Quotation qut where qut.quotno like '%"+enqType+"%"+da+"%' and qut.businessId=:busid";
				}else{
					qstr="select COUNT(distinct qut.enqid)+1 From Quotation qut where qut.quotno like '%"+enqType+"%"+yy+"%' and qut.businessId=:busid";
				}
			}
			Query qr=session.createQuery(qstr);
			qr.setInteger("busid", busid);
			Object qresult=qr.uniqueResult();
			long i=(Long)qresult;
			return i;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}

	}


	public int gtqtid(int enqid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
		
			Connection con = session.connection();
			Statement stm = con.createStatement();
			String query="select count(*)+1 from Quotation  where enqid="+enqid+" and business_id="+busid;
			logger.info("the query "+query);
			ResultSet res = stm.executeQuery(query);
			ResultSetMetaData rsmd = res.getMetaData();
			int rowCount=0;  
			  int currentRow = res.getRow();
			  // Get current row  
			   rowCount = res.last() ? res.getRow() : 0; // Determine number of rows  
			   if (currentRow == 0)  
			   {// If there was no current row  
			      res.beforeFirst();
			   }// We want next() to go to first row  
			   else
			   {
				  
			   
			      res.absolute(currentRow);      
			   }
			   
			logger.info("Number of Columns="+rowCount);
			logger.info("Number of Columns="+res);
			return rowCount;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	
	public  Business getshrtname(String cmp)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Business where compname=:cmp";
			Query query=session.createQuery(querystr);
			query.setString("cmp", cmp);
			Object queryresult=query.uniqueResult();
			Business comp=(Business)queryresult;
			return comp;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  Parts getpartid(String prtnum, int enqid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Parts where prtnum=:prtnum or description=:prtnum and enqid=:eid";
			Query query=session.createQuery(querystr);
			query.setString("prtnum", prtnum);
			query.setInteger("eid", enqid);
			Object queryresult=query.uniqueResult();
			Parts comp=(Parts)queryresult;
			return comp;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  Business getaddr(String quotby) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Business where compname=:shrtname";
			Query query=session.createQuery(querystr);
			query.setString("shrtname", quotby);
			Object queryresult=query.uniqueResult();
			Business comp=(Business)queryresult;
			return comp;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Long getquots(String month,String year) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="select count(distinct q.enqid) From Quotation q where qutdate like '"+year+"-"+month+"%'";
			Query query=session.createQuery(querystr);
			Long quots=(Long) query.uniqueResult();
			return quots;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	
	
	public  List<Quotation> getquot(int enqid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where enqid=:enqid";
			Query query=session.createQuery(querystr);
			query.setInteger("enqid", enqid);
			List<Quotation> queryresult=(List<Quotation>)query.list();
			return queryresult;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Object> getquotparts(int quotid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From QuotParts  where quotid=:quotid";
			Query query=session.createQuery(querystr);
			query.setInteger("quotid", quotid);
			Object queryresult=query.list();
			List<Object> quotparts=(List<Object>) queryresult;
			return quotparts;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Quotation> getQuotByEnq(int enqid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			 List<Quotation> custObjs=new ArrayList<Quotation>();
			 List objs=null;
			String querystr=" From Quotation  where id=(select max(id) from Quotation where business_id=:busid and enqid=:enqid)";
			Query query=session.createQuery(querystr);
			query.setInteger("enqid", enqid);
			query.setInteger("busid",busid);
			objs= query.list();
			for(Object o:objs){
				custObjs.add((Quotation)o);
			}
			return custObjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
		}
	public  List<Quotation> getQuotCount(int enqid,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where business_id=:busid and enqid=:enqid";
			Query query=session.createQuery(querystr);
			query.setInteger("enqid", enqid);
			query.setInteger("busid",busid);
			List<Quotation>  custobjs =(List<Quotation> ) query.list();
			return custobjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Quotation getQuotByQuotNo(int enqid,String quotno,int busid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Object objs=null;
			String querystr="From Quotation  where enqid=:enqid and  business_id=:busid and quotno=:quotno";
			Query query=session.createQuery(querystr);
			query.setInteger("enqid", enqid);
			query.setString("quotno",quotno);
			query.setInteger("busid",busid);
			objs= query.uniqueResult();
			Quotation quot=(Quotation)objs;
			return quot;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
		}
	public  ArrayList<QuotParts> getPartsByQuotIdPartId(int quotid,int partid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From QuotParts  where quotno=:quotid and prtnum=:partid";
			Query query=session.createQuery(querystr);
			query.setInteger("quotid", quotid);
			query.setInteger("partid", partid);		
			Object queryresult=query.list();
			ArrayList<QuotParts> quotparts=(ArrayList<QuotParts>) queryresult;
			return quotparts;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Opportunity getenq(String quotid) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where quotno=:quotid";
			Query query=session.createQuery(querystr);
			query.setString("quotid", quotid);
			Object queryresult=query.uniqueResult();
			Opportunity quotparts=(Opportunity) queryresult;
			return quotparts;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<Object> allQuotations() throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation";
			Query query=session.createQuery(querystr);
			Object queryresult=query.list();
			List<Object> quots=(List<Object>) queryresult;
			return quots;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  Quotation getByQuotid(String quot) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where quotno=:quotid";
			Query query=session.createQuery(querystr);
			query.setString("quotid", quot);
			Object queryresult=query.uniqueResult();
			Quotation q=(Quotation) queryresult;
			return q;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Quotation getByid(int id) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where id=:id";
			Query query=session.createQuery(querystr);
			query.setInteger("id", id);
			Object queryresult=query.uniqueResult();
			Quotation q=(Quotation) queryresult;
			return q;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  List<Object> getyrQuts(String year) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="select count(distinct q.enqid) From Quotation q where qutdate like '"+year+"%'";
			Query query=session.createQuery(querystr);
			Object queryresult=query.list();
			List<Object> quots=(List<Object>) queryresult;
			return quots;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  List<Object> quotationsforPO() throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="Select DISTINCT enqid From Quotation order by ENQID DESC";
			Query query=session.createQuery(querystr);
			Object queryresult=query.list();
			List<Object> quots=(List<Object>)queryresult;
			return quots;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}

	public  Quotation getquotid(int enqid, String quotNum,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where enqid=:id and quotno=:quotNm and business_id=:busid";
			Query query=session.createQuery(querystr);
			query.setInteger("id", enqid);
			query.setString("quotNm", quotNum);
			query.setInteger("busid",busid);
			Object queryresult=query.uniqueResult();
			Quotation q=(Quotation) queryresult;
			return q;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Quotation getPendingQuot(int enqid,int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where id=(select max(id) from Quotation where business_id=:busid and enqid=:enqid)";
			Query query=session.createQuery(querystr);
			query.setInteger("enqid", enqid);
			query.setInteger("busid",busid);
			Object queryresult=query.uniqueResult();
			Quotation q=(Quotation) queryresult;
			return q;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			//session.close();
		}
	}
	public  List<Quotation> getByCompShrtName(String shortname) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Quotation  where ordrBy=:shortname";
			Query query=session.createQuery(querystr);
			query.setString("shortname", shortname);
			Object queryresult=query.list();
			List<Quotation> q=(List<Quotation>) queryresult;
			return q;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
		
	}
	public  List<Quotation> getQuotNumByEnq(int busid,int refid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			List<Quotation> custobjs=new ArrayList<Quotation>();
			List<Quotation> lquot=new ArrayList<Quotation>();
			logger.info("the ref id and busid is"+refid+"....");
			String qryStr="From Quotation  where id=(select max(id) from Quotation where enqid=:rfNo and business_id=:busid)";
			Query qry=session.createQuery(qryStr);
			qry.setInteger("rfNo", refid);
		    qry.setInteger("busid",busid);
			custobjs=qry.list();
			logger.info("the list of quotation in dao is"+custobjs);
			for(Object o:custobjs){
				lquot.add((Quotation)o);
				logger.info("the list of quotation"+o);
			}
			List<Quotation> q=(List<Quotation>)qry.list();
			return custobjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
	public  Quotation getRecentQuotNumByEnq(int busid,int refid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Quotation custobjs=new Quotation();
			System.out.println("the ref id and busid is"+refid+"....");
			String qryStr="from Quotation q where enqid=:rfNo and business_id=:busid order by q.qutdate asc LIMIT 1";
			Query qry=session.createQuery(qryStr);
			qry.setInteger("rfNo", refid);
		    qry.setInteger("busid",busid);
		    qry.setMaxResults(1);
			custobjs=(Quotation) qry.uniqueResult();
			System.out.println("the list of quotation in dao is"+custobjs);
			return custobjs;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
		}
	}
private  Session startOperations()throws Exception {
		
		Session session = ApplicationContext.getHiberSession().openSession();
		tx=session.beginTransaction();
		return session;
		
	}
	public List<Quotation> getQuotaion(int businessId) throws Exception {
		List<Quotation> ob=new ArrayList<Quotation>();
		Session session = startOperations();
		try {
		String qrystr="From Quotation where enqid=:id ";
		
		Query qr=session.createQuery(qrystr);
		qr.setInteger("id", businessId);
		ob=qr.list();
		
		return ob;
		}
		catch (Exception e) {
			throw e;
		}
		finally {
			session.close();
			session=null;
		}
	}	
}
